<!DOCTYPE html>
<html>
<head>
    <title>07.高级查询-排序 多表联合 聚合与分组 限制条数</title>

    <meta charset="UTF-8"/>

    <meta content="text/html; charset=utf-8" http-equiv="Content-Type"/>

    <meta name="HandheldFriendly" content="true"/>

    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no"/>

    <meta name="apple-mobile-web-app-capable" content="yes"/>

    <meta name="apple-mobile-web-app-status-bar-style" content="black"/>

    <link href="./asset/farbox/basic.css" type="text/css" rel="stylesheet"/>
    <link href="./asset/farbox/main.css" type="text/css" rel="stylesheet"/>

    <style type="text/css">
        
            span.md_line{margin-bottom:0.5em; display:block; line-height:1.89}
            .md_line br{ display: none;}
            
    </style>

    <script>
        var flowchat_options = {
            'x': 0, 'y': 0, 'line-width': 1, 'line-length': 50, 'text-margin': 10, 'font-size': 13,
            'font-color': '#3c3c3c', 'line-color': '#666666', 'element-color': '#666666', 'fill': 'transparent',
            'yes-text': 'yes', 'no-text': 'no', 'arrow-end': 'block', 'class': 'flowchart', 'scale': 1,
            'symbols': { 'start': {}, 'end': {}, 'condition': {}, 'inputoutput': {}, 'operation': {}, 'subroutine': {}}
        }
    </script>
    <script type="text/javascript" src="./asset/other/raphael-min.js"></script>
    <script type="text/javascript" src="./asset/other/flowchart.js"></script>
    <script type="text/javascript" src="./asset/other/echarts.min.js"></script>


</head>

<body>
    
        <a href="./index.html" id="go_to_home"> &lt; home </a>
    
    <div id="sidebar">
        <ul id="sidebar_body">
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./001.软件下载安装配置.html" class="">第一章 软件下载安装配置</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./002.数据库操作.html" class="">002.数据库操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./003.数据表操作.html" class="">003.数据表操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./004.PHP与sql server连接.html" class="">004.PHP与sql server连接</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./005.数据的增删改.html" class="">005.数据的增删改</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./006.数据操作-简单查询.html" class="">006.数据操作-简单查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./007.高级查询.html" class="selected">07.高级查询-排序 多表联合 聚合与分组 限制条数</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./008.多表连接查询.html" class="">08.多表连接查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./009.子查询 ANY ALL [NOT] IN [NOT]EXISTS.html" class="">09.子查询 ANY ALL [NOT] IN [NOT]EXISTS</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./010.事务-索引-视图.html" class="">010.事务-索引-视图</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./011.T-SQL.html" class="">011.T-SQL学习</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./012.存储过程.html" class="">012.存储过程</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./013.触发器.html" class="">10.触发器</a>
                </li>
            

            
        
        </ul>
    </div>

    <div id="main">
        <div class="content_body">
            <h1 class="title"> 07.高级查询-排序 多表联合 聚合与分组 限制条数 </h1>
            <div class="doc_post"> <p class="md_block">

<div class="toc"><ul>
<li>
<a href="#toc_0">07.高级查询-排序 多表联合 聚合与分组 限制条数</a>
<ul>
<li>
<a href="#toc_1">排序 ORDER BY 列名 [ASC]|DESC</a>
</li>
<li>
<a href="#toc_2">限制条数 TOP ?</a>
</li>
<li>
<a href="#toc_3">多结果合并 UNION [ALL]</a>
</li>
<li>
<a href="#toc_4">聚合COUNT(),MAX(),MIN(),SUM(),AVG() 配合 GROUP BY 列名[,列名...]</a>
</li>
<li>
<a href="#toc_5">聚合后二次筛选 HAVING 条件</a>
</li>
</ul>
</li>
</ul>
</div>
</p>


<h2 id="toc_1">排序 ORDER BY 列名 [ASC]|DESC</h2>

<p class="md_block">
    <span class="md_line">一般写在查询语句结尾</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span><span class="n">uname</span><span class="p">,</span><span class="n">subject</span><span class="p">,</span><span class="n">score</span> <span class="k">FROM</span> <span class="err">表名</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="err">列名</span> <span class="k">ASC</span><span class="p">;</span>
<span class="c1">-- ASC是默认值,可以不写,也加以加上,如果用降序,DESC就必须写上</span>
<span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span><span class="n">uname</span><span class="p">,</span><span class="n">subject</span><span class="p">,</span><span class="n">score</span> <span class="k">FROM</span> <span class="err">表名</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="err">列名</span><span class="p">;</span>
<span class="c1">-- 注意多列排序, ORDER BY 列名 ASC,列名 DESC</span>
<span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span><span class="n">uname</span><span class="p">,</span><span class="n">subject</span><span class="p">,</span><span class="n">score</span> <span class="k">FROM</span> <span class="err">表名</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">score</span> <span class="k">DESC</span><span class="p">,</span> <span class="n">id</span><span class="p">;</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_2">限制条数 TOP ?</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 限制查询结果记录条数</span>
<span class="k">SELECT</span> <span class="n">TOP</span> <span class="mi">3</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">id</span> <span class="k">DESC</span><span class="p">;</span>

<span class="c1">-- 分页(先练习,后面&#39;子查询&#39;再讲一次)</span>
<span class="k">DECLARE</span> <span class="o">@</span><span class="n">page_num</span> <span class="nb">INT</span> <span class="o">=</span> <span class="mi">3</span><span class="p">;</span>  <span class="c1">-- 第?页</span>
<span class="k">DECLARE</span> <span class="o">@</span><span class="n">page_count</span> <span class="nb">INT</span> <span class="o">=</span> <span class="mi">3</span><span class="p">;</span> <span class="c1">-- 每页显示?条</span>
<span class="k">SELECT</span> <span class="n">TOP</span> <span class="p">(</span><span class="o">@</span><span class="n">page_count</span><span class="p">)</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="k">NOT</span> <span class="k">IN</span><span class="p">(</span><span class="k">SELECT</span> <span class="n">TOP</span> <span class="p">((</span><span class="o">@</span><span class="n">page_num</span><span class="o">-</span><span class="mi">1</span><span class="p">)</span><span class="o">*@</span><span class="n">page_count</span><span class="p">)</span> <span class="n">id</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">id</span><span class="p">)</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">id</span><span class="p">;</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_3">多结果合并 UNION [ALL]</h2>

<p class="md_block">
    <span class="md_line">1.用于合并两个或多个 SELECT 语句的结果集<br /></span>
    <span class="md_line">2.注意:UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。<br /></span>
    <span class="md_line">3.UNION 与 UNION ALL基本是等效的,但UNION不会显示重复数据.而UNION ALL会显示重复数据.</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 查询结果合并 UNION会自动去重复  而 UNION ALL则不会去重复</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">1</span>
<span class="k">UNION</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">WHERE</span> <span class="n">ID</span>  <span class="o">=</span> <span class="mi">10</span>
<span class="k">UNION</span> <span class="k">ALL</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">WHERE</span> <span class="n">sex</span> <span class="o">=</span> <span class="mi">0</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_4">聚合COUNT(),MAX(),MIN(),SUM(),AVG() 配合 GROUP BY 列名[,列名...]</h2>

<p class="md_block">
    <span class="md_line">COUNT() 统计记录条数,整数<br /></span>
    <span class="md_line">MAX() 最大值<br /></span>
    <span class="md_line">MIN() 最小值<br /></span>
    <span class="md_line">SUM() 求和<br /></span>
    <span class="md_line">AVG() 求平均值<br /></span>
    <span class="md_line">一般在实际开发中,以上函数都要配合(GROUP BY 列名) 进行分组查询.除非是单列对整个结果集进行统计.   </span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 聚合与分组(COUNT(),SUM(),MAX(),MIN(),AVG()给合GROUP BY 列名,列名....)</span>

<span class="k">SELECT</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">WHERE</span> <span class="n">sex</span> <span class="o">=</span> <span class="mi">1</span><span class="p">;</span>
<span class="c1">-- COUNT()统计记录条数,注意,统计条件时,如果指定列统计,NULL不计入统计.</span>
<span class="k">SELECT</span> <span class="k">COUNT</span><span class="p">(</span><span class="n">class_id</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">WHERE</span> <span class="n">sex</span> <span class="o">=</span> <span class="mi">1</span><span class="p">;</span>

<span class="c1">-- SUM()求和</span>
<span class="k">SELECT</span> <span class="k">SUM</span><span class="p">(</span><span class="n">status</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">stu_info</span><span class="p">;</span>

<span class="c1">--MAX()最大值</span>
<span class="k">SELECT</span> <span class="k">MAX</span><span class="p">(</span><span class="n">id</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">stu_info</span><span class="p">;</span>

<span class="c1">--MIN() 最小值</span>
<span class="k">SELECT</span> <span class="k">MIN</span><span class="p">(</span><span class="n">id</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">stu_info</span><span class="p">;</span>

<span class="c1">-- AVG()平均值</span>
<span class="k">SELECT</span> <span class="k">AVG</span><span class="p">(</span><span class="n">id</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">stu_info</span><span class="p">;</span>

<span class="c1">-- 分组与统计结合使用</span>
<span class="k">SELECT</span> <span class="n">dorm_id</span><span class="p">,</span><span class="k">COUNT</span><span class="p">(</span><span class="n">id</span><span class="p">)</span> <span class="k">AS</span> <span class="s1">&#39;人数&#39;</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">dorm_id</span>
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">GROUP_CONCAT()函数用法:得到某列分组内容详细情况.<br /></span>
    <span class="md_line">实例:查询出每个人参考科目数,(显示用户ID列,参考科目数,参考了哪几科???详细情况)</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">select</span> <span class="o">*</span> <span class="k">from</span> <span class="n">test</span><span class="p">;</span>
<span class="k">select</span> <span class="n">group_concat</span><span class="p">(</span><span class="n">id</span><span class="p">),</span><span class="n">group_concat</span><span class="p">(</span><span class="n">uname</span><span class="p">),</span><span class="n">grop</span><span class="p">,</span><span class="n">sub</span><span class="p">,</span><span class="n">score</span> <span class="k">from</span> <span class="n">test1</span> <span class="k">group</span> <span class="k">by</span> <span class="n">grop</span><span class="p">;</span>
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">配合WITH ROLLUP记录上面所有记录的总和</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 见上面UNION节,要求分组统计的同时做好相关列的再次统计</span>
<span class="k">SELECT</span> <span class="n">grop</span><span class="p">,</span><span class="n">uname</span><span class="p">,</span><span class="k">AVG</span><span class="p">(</span><span class="n">score</span><span class="p">)</span> <span class="k">from</span> <span class="n">test</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">grop</span><span class="p">,</span><span class="n">uname</span>
<span class="k">UNION</span>
<span class="k">SELECT</span> <span class="n">grop</span><span class="p">,</span><span class="s1">&#39;小组平均分&#39;</span><span class="p">,</span><span class="k">AVG</span><span class="p">(</span><span class="n">score</span><span class="p">)</span> <span class="k">from</span> <span class="n">test</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">grop</span> 
<span class="k">UNION</span>
<span class="k">select</span> <span class="s1">&#39;总平均分&#39;</span><span class="p">,</span><span class="k">NULL</span><span class="p">,</span><span class="k">avg</span><span class="p">(</span><span class="n">score</span><span class="p">)</span> <span class="k">from</span> <span class="n">test</span>
<span class="k">order</span> <span class="k">by</span> <span class="n">grop</span><span class="p">,</span><span class="n">uname</span> <span class="k">desc</span><span class="p">;</span>
<span class="c1">-- 注意,上列语句在MYSQL中可以写成如下格式,在分组统计时用 WITH ROLLUP</span>
<span class="k">SELECT</span> <span class="n">grop</span><span class="p">,</span><span class="n">uname</span><span class="p">,</span><span class="k">AVG</span><span class="p">(</span><span class="n">score</span><span class="p">)</span> <span class="k">from</span> <span class="n">test</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">grop</span><span class="p">,</span><span class="n">uname</span> <span class="k">WITH</span> <span class="k">ROLLUP</span><span class="p">;</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_5">聚合后二次筛选 HAVING 条件</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 查询结果一旦使用聚合列后,如果结果想再次进行筛选,要配合HAVING使用.二次筛选</span>
<span class="k">SELECT</span> <span class="n">grop</span><span class="p">,</span><span class="k">null</span><span class="p">,</span><span class="k">AVG</span><span class="p">(</span><span class="n">score</span><span class="p">)</span> <span class="k">from</span> <span class="n">test</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">grop</span> <span class="k">HAVING</span> <span class="k">AVG</span><span class="p">(</span><span class="n">score</span><span class="p">)</span> <span class="o">&gt;=</span> <span class="mi">80</span><span class="p">;</span>
<span class="c1">-- HAVING语句可以用如下语句取代.</span>
<span class="k">select</span> <span class="o">*</span> <span class="k">from</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">grop</span><span class="p">,</span><span class="k">null</span><span class="p">,</span><span class="k">AVG</span><span class="p">(</span><span class="n">score</span><span class="p">)</span> <span class="k">as</span> <span class="n">savg</span> <span class="k">from</span> <span class="n">test</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">grop</span><span class="p">)</span> <span class="n">a</span> <span class="k">where</span> <span class="n">savg</span> <span class="o">&gt;=</span> <span class="mi">80</span><span class="p">;</span>
</pre></div>

<!--block_code_end--> </div>
        </div>

    </div>





<script>
    // auto focus for menu
    window.onload = function(){
        var sidebar = document.getElementById('sidebar');
        var current = document.getElementsByClassName('selected')[0];
        if (current && sidebar){
            sidebar.scrollTop = current.offsetTop - sidebar.clientHeight/2 + 100
        }
    };
</script>




</body>

</html>